const db=require('./db.js');
const fs=require('fs');
//登录验证
exports.login=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    console.log(str);
    let sql='select * from	(select userinfo.*,`user`.upwd from userinfo,`user` where userinfo.username=`user`.uname) as newuserinfo where username=? and upwd=?';
    let data=[str.username,str.password];
    db.base(sql,data,(result)=>{
        console.log(result);
        console.log(result.length);
        if(result.length!=0){
            res.json({flag:1,userinfo:result});
        }else{
            res.json({flag:2});
        }
    });
}
// //获取用户信息
exports.userinfo=(req,res)=>{
    let id=req.params.uid;
    let sql="select * from	(select userinfo.*,`user`.upwd from userinfo,`user` where userinfo.username=`user`.uname) as newuserinfo where uid='"+id+"'";
    db.base(sql,null,(result)=>{
        res.json(result);
    });
}
//保存上传的头像
let url='';
exports.upload=(req,res)=>{
    console.log(req.file);
    let file=req.file;
    fs.renameSync('./public/img/' + file.filename,'./public/img/' + file.originalname);
    res.json({imgurl:'http://localhost:5050/img/'+file.originalname});
    url='http://localhost:5050/img/'+file.originalname;
}
//修改头像信息
exports.updataimgurl=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    let sql="select imgurl from userinfo where uid=?";
    let data=[str.uid];
    db.base(sql,data,(result)=>{
        console.log(result[0].imgurl);
        let deletepath=result[0].imgurl;
        deletepath=deletepath.split('http://localhost:5050/img/')[1];
        console.log(deletepath);
        let sql1="update userinfo set imgurl=? where uid=?";
        let data1=[url,str.uid];
        db.base(sql1,data1,(result)=>{
            console.log(result);
            if(result.affectedRows>=1){
                fs.unlink('./public/img/'+deletepath, (err) => {
                    if (err) throw err;
                    console.log('原头像删除成功');
                });
                res.json({flag:1,imgurl:url});
            }else{
                res.json({flag:2});
            }
        });      
    });
}
//修改用户信息
exports.updatainfo=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    let sql="update userinfo,`user` set userinfo.username=?,userinfo.phone=?,userinfo.email=?,userinfo.sex=?,`user`.uname=? where userinfo.uid=?";
    let data=[str.name,str.phone,str.email,str.sex,str.name,str.uid];
    db.base(sql,data,(result)=>{
        console.log(result);
        if(result.affectedRows>=1){
            res.json({flag:1});
        }else{
            res.json({flag:2});
        }
    });
}
//修改用户密码
exports.updatapwd=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    let sql="update `user` set `user`.upwd=? where `user`.uid=?";
    let data=[str.pwd,str.uid];
    db.base(sql,data,(result)=>{
        console.log(result);
        if(result.affectedRows>=1){
            res.json({flag:1,pwd:str.pwd});
        }else{
            res.json({flag:2});
        }
    });
}
//获取所有部门信息
exports.getAlldepartment=(req,res)=>{
    let sql='select *from department';
    db.base(sql,null,(result)=>{
        res.json(result);
    });
}
//获取所有职位信息
exports.getAllposition=(req,res)=>{
    let sql='select *from position';
    db.base(sql,null,(result)=>{
        res.json(result);
    });
}
//部门搜索功能
exports.seach=(req,res)=>{
    let id=req.params.id;
    let sql="select * from department where dname like '%"+ id +"%'";
    db.base(sql,null,(result)=>{
        console.log(result);
        res.json(result);
    });
}
//职位搜索功能
exports.seach1=(req,res)=>{
    let id=req.params.id;
    let sql="select * from position where pname like '%"+ id +"%'";
    db.base(sql,null,(result)=>{
        console.log(result);
        res.json(result);
    });
}
//员工薪资信息搜索功能
exports.seach2=(req,res)=>{
    let id=req.params.id;
    let sql="select * from salary where sname like '%"+ id +"%'";
    db.base(sql,null,(result)=>{
        console.log(result);
        res.json(result);
    });
}
//员工信息搜索功能
exports.seach3=(req,res)=>{
    let id=req.params.id;
    let sql="select * from information where iname like '%"+ id +"%'";
    db.base(sql,null,(result)=>{
        console.log(result);
        res.json(result);
    });
}
//修改部门信息
exports.updatedepartment=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    console.log(str);
    let sql='update department set dname=?,dsynopsis=? where did=?';
    let data=[str.dname,str.dsynopsis,str.did];
    db.base(sql,data,(result)=>{
        console.log(result);
        if(result.affectedRows>=1){
            res.json({flag:1});
        }else{
            res.json({flag:2});
        }
    });
    let sql1='update position set pdname=? where pdid=?';
    let sql2='update information set idname=? where idid=?';
    let sql3='update salary set sdname=? where sdid=?';
    let sqls=[sql1,sql2,sql3];
    let data1=[str.dname,str.did];
    for(var i=0;i<sqls.length;i++){
        db.base(sqls[i],data1,(result)=>{});
    }
}
//修改职位信息
exports.updateposition=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    console.log(str);
    let sql='update position set pname=?,pdid=?,pdname=?,psynopsis=? where pid=?';
    let data=[str.pname,str.pdid,str.pdname,str.psynopsis,str.pid];
    db.base(sql,data,(result)=>{
        console.log(result);
        if(result.affectedRows>=1){
            res.json({flag:1});
        }else{
            res.json({flag:2});
        }
    });
    let sql1='update information set ipname=? where ipid=?';
    let sql2='update salary set spname=? where spid=?';
    let sqls1=[sql1,sql2];
    let data1=[str.pname,str.pid];
    for(var i=0;i<sqls1.length;i++){
        db.base(sqls1[i],data1,(result)=>{});
    }
    let sql3='update information set idname=?,idid=? where ipid=?';
    let sql4='update salary set sdname=?,sdid=? where spid=?';
    let sqls2=[sql3,sql4];
    let data2=[str.pdname,str.pdid,str.pid];
    for(var i=0;i<sqls2.length;i++){
        db.base(sqls2[i],data2,(result)=>{});
    }
}
//添加部门信息
exports.adddepartment=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    console.log(str);
    let sql='insert into department (dname,dtime,dfounder,dmanager,dsynopsis) values (?,?,?,?,?)';
    let data=[str.dname,str.dtime,str.dfounder,str.dmanager,str.dsynopsis];
    db.base(sql,data,(result)=>{
        console.log(result);
        if(result.affectedRows>=1){
            res.json({flag:1});
        }else{
            res.json({flag:2});
        }
    });
}
//添加职位信息
exports.addposition=(req,res)=>{
    let info=req.body;
    let str1=JSON.stringify(info);
    str1=JSON.parse(str1);
    console.log(str1);
    let sql='INSERT position set pname=?,ptime=?,pfounder=?,pdid=?,pdname=?,psynopsis=?';
    let data=[str1.pname,str1.ptime,str1.founder,str1.pdid,str1.pdname,str1.psynopsis];
    db.base(sql,data,(result)=>{
        console.log(result);
        if(result.affectedRows>=1){
            res.json({flag:1});
        }else{
            res.json({flag:2});
        }
    });
}
//删除部门信息
exports.deletedepartment=(req,res)=>{
    let id=req.params.id;
    let sql="delete from department where did=?";
    let data=[id];
    console.log(data);
    db.base(sql,data,(result)=>{
        if(result.affectedRows>=1){
            res.json({flag:1});
        }else{
            res.json({flag:2});
        }
    });
    let sql5='select * from position where pdid=?';
    db.base(sql5,data,(result)=>{
        if(result.length!=0){
            let sql1="delete from position where pdid=?";
            let sql2="update information set idid=null,idname=null,ipid=null,ipname=null where idid=?";
            let sql3="update salary set sdid=null,sdname=null,spid=null,spname=null where sdid=?";
            let sqls=[sql1,sql2,sql3];
            for(var i=0;i<sqls.length;i++){
                db.base(sqls[i],data,(result)=>{});
            }
        }
    })
}
//删除职位信息
exports.deleteposition=(req,res)=>{
    let id=req.params.id;
    let sql="delete from position where pid=?";
    let data=[id];
    console.log(data);
    db.base(sql,data,(result)=>{
        if(result.affectedRows>=1){
            res.json({flag:1});
        }else{
            res.json({flag:2});
        }
    });
    let sql1="update information set ipid=null,ipname=null where ipid=?";
    let sql2="update salary set spid=null,spname=null where spid=?";
    let sqls=[sql1,sql2];
    for(var i=0;i<sqls.length;i++){
        db.base(sqls[i],data,(result)=>{});
    }
}
//设置经理
exports.setdmanager=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    console.log(str);
    let sql1='select pid from position where pdid=? and pname=?';
    let data1=[str.did,str.pname];
    db.base(sql1,data1,(result)=>{      
        if(result.length==0){
            let sql2='INSERT position set pname=?,ptime=?,pfounder=?,pdid=?,pdname=?,psynopsis=?';
            let data2=[str.pname,str.dtime,str.dfounder,str.did,str.dname,null];
            db.base(sql2,data2,(result)=>{
                if(result.affectedRows>=1){
                    let sql4='select pid from position where pdid=? and pname=?';
                    let data4=[str.did,str.pname];
                    db.base(sql4,data4,(result)=>{
                        let pid1=JSON.parse(JSON.stringify(result))[0].pid;
                        let sql3='update department,information,salary set department.diid=null,department.dmanager=null,information.ipid=null,information.ipname=null,salary.spid=null,salary.spname=null where department.diid=? and information.iid=? and salary.sid=?';
                        let data3=[str.iid,str.diid,str.diid];
                        db.base(sql3,data3,(result)=>{
                            let sql4='update department set department.diid=null,department.dmanager=null where department.diid=?';
                            let data4=[str.iid];
                            db.base(sql4,data4,(result)=>{
                                let sql='update department,information,salary set department.diid=?,department.dmanager=?,information.ipid=?,information.ipname=?,information.idid=?,information.idname=?,salary.spid=?,salary.spname=?,salary.sdid=?,salary.sdname=? where department.did=? and information.iid=? and salary.sid=?';
                                let data=[str.iid,str.dmanager,pid1,str.pname,str.did,str.dname,pid1,str.pname,str.did,str.dname,str.did,str.iid,str.iid];
                                db.base(sql,data,(result)=>{
                                    if(result.affectedRows>=1){
                                        res.json({flag:1,cp:1});
                                    }else{
                                        res.json({flag:2,cp:1});
                                    }
                                });  
                            });
                        });  
                    });               
                }else{
                    res.json({cp:2});
                }
            });
        }else{
            let pid=JSON.parse(JSON.stringify(result))[0].pid;
            let sql3='update department,information,salary set department.diid=null,department.dmanager=null,information.ipid=null,information.ipname=null,salary.spid=null,salary.spname=null where department.diid=? and department.dmanager=? and information.iid=? and salary.sid=?';
            let data3=[str.iid,str.dmanager,str.diid,str.diid];
            db.base(sql3,data3,(result)=>{
                let sql4='update department set department.diid=null,department.dmanager=null where department.diid=?';
                let data4=[str.iid];
                db.base(sql4,data4,(result)=>{
                    let sql='update department,information,salary set department.diid=?,department.dmanager=?,information.ipid=?,information.ipname=?,information.idid=?,information.idname=?,salary.spid=?,salary.spname=?,salary.sdid=?,salary.sdname=? where department.did=? and information.iid=? and salary.sid=?';
                    let data=[str.iid,str.dmanager,pid,str.pname,str.did,str.dname,pid,str.pname,str.did,str.dname,str.did,str.iid,str.iid];
                    db.base(sql,data,(result)=>{
                        if(result.affectedRows>=1){
                            res.json({flag:1,cp:0});
                        }else{
                            res.json({flag:2,cp:0});
                        }
                    });  
                });
            });         
        }
    });
}
//获取所有员工薪资信息
exports.getAllsalary=(req,res)=>{
    let sql='select *from salary';
    db.base(sql,null,(result)=>{
        res.json(result);
    });
}
//获取所有员工信息
exports.getAllinformation=(req,res)=>{
    let sql='select *from information';
    db.base(sql,null,(result)=>{
        res.json(result);
    });
}
//添加员工信息
exports.addinformation=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    console.log(str); 
    let sql="INSERT information set iname=?,isex=?,iage=?,ination=?,idegree=?,idid=?,idname=?,ipid=?,ipname=?,ichildren=?,iloans=?,irenting=?,iparent=?";
    let data=[str.name,str.sex,str.age,str.nation,str.degree,str.did,str.dname,str.pid,str.pname,str.children,str.loans,str.renting,str.parent];
    db.base(sql,data,(result)=>{
        if(result.affectedRows>=1){
            let sql2='select iid from information where iname=? and idname=? and ipname=?';
            let data2=[str.name,str.dname,str.pname];
            db.base(sql2,data2,(result)=>{
                if(result!=null){
                    let iid= JSON.parse(JSON.stringify(result))[0].iid;
                    console.log(iid);
                    //个人需缴纳的五险一金 税前扣
                    let spremium=str.pay*0.22;
                    //税前基本工资
                    let shuiqian=str.pay;
                    //计算个人所得税
                    if(str.pay>5000){
                        str.pay=str.pay-5000;
                        if(str.children!=0){
                            //专项扣除 子女教育 每个子女1000元/月
                            str.pay=str.pay-str.children*1000
                        }
                        if(str.loans=='是'){
                            //专项扣除 贷款 1000元/月
                            str.pay=str.pay-1000
                        }
                        if(str.renting=='是'){
                            //专项扣除 租房 800元/月
                            str.pay=str.pay-800;
                        }
                        if(str.pay>0){
                            if(str.pay<=3000){
                                str.pay=str.pay*0.03
                            }else if(3000<str.pay<=12000){
                                str.pay=str.pay*0.1
                            }else if(12000<str.pay<=25000){
                                str.pay=str.pay*0.2
                            }else if(25000<str.pay<=35000){
                                str.pay=str.pay*0.25
                            }else if(35000<str.pay<=55000){
                                str.pay=str.pay*0.30
                            }else if(55000<=80000){
                                str.pay=str.pay*0.35
                            }else if(str.pay>80000){
                                str.pay=str.pay*0.45
                            }
                        }else{
                            str.pay=0;
                        }
                    }else{
                        str.pay=0;
                    }
                    let salary=parseInt(shuiqian)+200;
                    let netpayroll=salary-str.pay-spremium;
                    let sql1='insert salary set sid=?,sname=?,sdid=?,sdname=?,spid=?,spname=?,spay=?,spremium=?,subsidies=?,stax=?,ssalary=?,snetpayroll=?';
                    let data1=[iid,str.name,str.did,str.dname,str.pid,str.pname,shuiqian,spremium,200,str.pay,salary,netpayroll];
                    db.base(sql1,data1,(result)=>{
                        if(result.affectedRows>=1){
                            res.json({flag:1,sflag:1});
                        }else{
                            res.json({flag:1,sflag:0});
                        }
                    });
                }
            });
        }else{
            res.json({flag:2});
        }
    });
}
//删除员工信息
exports.deleteinformation=(req,res)=>{
    let id=req.params.id;
    let sql="delete from information where iid=?";
    let data=[id];
    console.log(data);
    db.base(sql,data,(result)=>{
        if(result.affectedRows>=1){
            let sql1="delete from salary where sid=?";
            db.base(sql1,data,(result)=>{
                if(result.affectedRows>=1){
                    res.json({flag:1});
                }else{
                    res.json({flag:2});
                }
            })
        }else{
            res.json({flag:2});
        }
    });
}
//修改员工信息
exports.updateinformation=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    console.log(str);
    let sql='update information set iname=?,isex=?,iage=?,ination=?,idegree=?,idid=?,idname=?,ipid=?,ipname=?,ichildren=?,iloans=?,irenting=?,iparent=? where iid=?';
    let data=[str.name,str.sex,str.age,str.nation,str.degree,str.did,str.dname,str.pid,str.pname,str.children,str.loans,str.renting,str.parent,str.iid];
    db.base(sql,data,(result)=>{
        console.log(result);
        if(result.affectedRows>=1){
            res.json({flag:1});
        }else{
            res.json({flag:2});
        }
    });
}
//修改员工薪资信息
exports.updatesalary=(req,res)=>{
    let info=req.body;
    let str=JSON.stringify(info);
    str=JSON.parse(str);
    console.log(str);
    let sql1="select ichildren,iloans,irenting,iparent from information where iid=?"
    let data1=[str.sid];
    db.base(sql1,data1,(result)=>{
        console.log(result);
        if(result!=null){
            let children= JSON.parse(JSON.stringify(result))[0].ichildren;
            let loans= JSON.parse(JSON.stringify(result))[0].iloans;
            let renting= JSON.parse(JSON.stringify(result))[0].irenting;
            let parent= JSON.parse(JSON.stringify(result))[0].iparent;
            //个人需缴纳的五险一金 税前扣
            let spremium=str.pay*0.22;
            let shuiqian=str.pay;
            let salary=parseInt(str.pay)+200;
            if(str.pay>5000){
                str.pay=str.pay-5000;
                if(children!=0){
                    //专项扣除 子女教育 每个子女1000元/月
                    str.pay=str.pay-str.children*1000
                }
                if(loans=='是'){
                    //专项扣除 贷款 1000元/月
                    str.pay=str.pay-1000
                }
                if(renting=='是'){
                    //专项扣除 租房 800元/月
                    str.pay=str.pay-800;
                }
                if(str.pay>0){
                    if(str.pay<=3000){
                        str.pay=str.pay*0.03
                    }else if(3000<str.pay<=12000){
                        str.pay=str.pay*0.1
                    }else if(12000<str.pay<=25000){
                        str.pay=str.pay*0.2
                    }else if(25000<str.pay<=35000){
                        str.pay=str.pay*0.25
                    }else if(35000<str.pay<=55000){
                        str.pay=str.pay*0.30
                    }else if(55000<=80000){
                        str.pay=str.pay*0.35
                    }else if(str.pay>80000){
                        str.pay=str.pay*0.45
                    }
                }else{
                    str.pay=0;
                }
            }else{
                str.pay=0;
            }
            let netpayroll=salary-spremium-str.pay;
            let sql='update salary set spay=?,spremium=?,stax=?,ssalary=?,snetpayroll=? where sid=?';
            let data=[shuiqian,spremium,str.pay,salary,netpayroll,str.sid];
            db.base(sql,data,(result)=>{
                console.log(result);
                if(result.affectedRows>=1){
                    res.json({flag:1});
                }else{
                    res.json({flag:2});
                }
            });
        }else{
            res.json({flag:2});
        }
    });
}